package com.steellee.leeexcel.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.data.DataFormatData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.steellee.leeexcel.abstruct.PaOwnCheckExtend;
import com.steellee.leeexcel.constraint.PaDropList;
import com.steellee.leeexcel.dto.*;
import com.steellee.leeexcel.exception.BusinessException;
import com.steellee.leeexcel.exception.MsgCode;
import com.steellee.leeexcel.msg.JSONMessage;
import com.steellee.leeexcel.util.CellStyleUtil;
import com.steellee.leeexcel.util.CustomCellWriteHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import javax.validation.*;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.*;
import java.util.List;

/**
 * Excel文件解析操作方法实现类 I
 * Bauthor steellee
 *
 * @date 2023-02-17
 */
public class PaExcelReadStart {

    private static final Logger logger = LoggerFactory.getLogger(PaExcelReadStart.class);
    public static final String ERR_MSG = "errMsg";
    public static final String ERR_INDEX = "errIndex";
    public static final String DATA = "data";
    public static final String SHEET_INFO = "sheetInfo";

    /**
     * 方案四: 入参对象集传递 Inputstream文件流必填, List<MetaExce1DTO>包含方案一参数集, 可读取多个 sheet★
     *
     * @param inputStream 入参文件流
     * @param param       入参MetaExcelDTO集合
     * @return
     */
    public JSONMessage read(InputStream inputStream, List<MetaExcelReadDTO> param) {
        long start = System.currentTimeMillis();
        Map<String, Object> result = new HashMap<>();
        List<List<Object>> excelData = new ArrayList<>();
        try {
            Map<String, Object> data = getMoreData(inputStream, param);
            if (!ObjectUtils.isEmpty(data.get("modelFlag")) && "true".equals(data.get("modelFlag").toString())) {
                return JSONMessage.success(MsgCode.FAIL_TEMP_ERR.code, MsgCode.FAIL_TEMP_ERR.msg);
            }
            if (!ObjectUtils.isEmpty(data.get("rowCheckFlag")) && "true".equals(data.get("rowCheckFlag").toString())) {
                return JSONMessage.success(MsgCode.CHECK_ROW_NUM_ERR.code, MsgCode.CHECK_ROW_NUM_ERR.msg);
            }
            result.put("excelData", data);
            excelData = (List<List<Object>>) data.get(DATA);
            for (int i = 0; i < excelData.size(); i++) {
                result.put(param.get(i).getResultClazz().getSimpleName(), excelData.get(i));
            }
        } catch (IndexOutOfBoundsException e) {
            return JSONMessage.success(MsgCode.FAIL_TEMP_ERR.code, MsgCode.FAIL_TEMP_ERR.msg);
        } catch (BusinessException e) {
            return JSONMessage.success(e.getCode(), e.getMessage());
        }
        logger.info("paExcelreadusetime:{}ms", System.currentTimeMillis() - start);
        return JSONMessage.success(MsgCode.FILE_ANALYSIS_SUCCESS.code, MsgCode.FILE_ANALYSIS_SUCCESS.msg, result);

    }

    public JSONMessage checkAndReturn(List<MetaExcelReadDTO> param, Map<String, Object> data, OutputStream outputStream, InputStream tempInputStream) {

        long start = System.currentTimeMillis();
        Map<String, Object> paramData = (Map<String, Object>) data.get("excelData");
        List<List<Object>> excelData = (List<List<Object>>) paramData.get(DATA);
        List<MetaSheetInfo> metaSheets = (List<MetaSheetInfo>) paramData.get(SHEET_INFO);
        Map<String, Object> result = new HashMap<>();
        Map<String, Object> checkMsg = new HashMap<>();
        String errMsg = "";
        String errIndex = "";
        for (int i = 0; i < excelData.size(); i++) {
            MetaExcelReadDTO dto = param.get(i);
            if (!ObjectUtils.isEmpty(excelData.get(i))) {
                checkMsg = checkValue(metaSheets.get(i), excelData.get(i), dto.getDataStartRowNo(), i, checkMsg, dto);
            }
            result.put(param.get(i).getResultClazz().getSimpleName(), excelData.get(i));
        }
        if (!ObjectUtils.isEmpty(checkMsg)) {
            errMsg = checkMsg.get(ERR_MSG).toString();
            errIndex = checkMsg.get(ERR_INDEX).toString();
        }

        //获取业务方个性化校验结果
        Map<String, String> errReturn = getOwnCheckResult(errMsg, errIndex, result, metaSheets, param.get(0).getAbstractClazz());
        if (!ObjectUtils.isEmpty(errReturn)) {
            errIndex = errReturn.get(ERR_INDEX);
            errMsg = errReturn.get(ERR_MSG);
        }
        if (!StringUtils.isEmpty(errMsg)) {
            logger.info("errspace:{}", errMsg);
            if (null != outputStream) {
                setFailCell(metaSheets, errIndex);
                if (null == tempInputStream) {
                    writeExcel(outputStream, metaSheets, param, result);
                } else {
                    //通过模板写出
                    writeExcelByTemp(outputStream, metaSheets, param, result, tempInputStream);
                }
            }
            Map<String, String> resultMap = new HashMap<>();
            resultMap.put("errIndex", errIndex);
            resultMap.put("errMsg", errMsg);
            return JSONMessage.success(MsgCode.CHECK_NOT_NULL_ERR.code, "校验存在异常!", resultMap);
        }
        logger.info("paExcelcheckAndReturnusetime:[}ms", System.currentTimeMillis() - start);
        return JSONMessage.success(MsgCode.FILE_ANALYSIS_SUCCESS.code, MsgCode.FILE_ANALYSIS_SUCCESS.msg, data);
    }

    private void writeExcelByTemp(OutputStream outputStream, List<MetaSheetInfo> metaSheets, List<MetaExcelReadDTO> param, Map<String, Object> result, InputStream tempInputStream) {
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream);
        try {
            ExcelWriter excelWriter = excelWriterBuilder.withTemplate(tempInputStream).build();
            List<Integer> sheetNos = new ArrayList<>();
            for (MetaExcelReadDTO dto : param) {
                List<Object> data = (List<Object>) result.get(dto.getResultClazz().getSimpleName());
                Map<Integer, String[]> finalChoiceMap = new HashMap<>();
                Field[] declaredFields = dto.getResultClazz().getDeclaredFields();
                Integer lastRowNum = dto.getDataStartRowNo() + data.size() - 1;
                for (int i = 0; i < declaredFields.length; i++) {
                    Field field = declaredFields[i];
                    PaDropList explicitConstraint = field.getAnnotation(PaDropList.class);
                    if (null != explicitConstraint) {
                        String[] source = explicitConstraint.source();
                        if (source.length > 0) {
                            finalChoiceMap.put(explicitConstraint.indexNum(), source);
                        }
                    }
                }
                WriteSheet writeSheet = EasyExcel.writerSheet(dto.getSheetNo()).head(dto.getResultClazz()).needHead(false)
                        .registerWriteHandler(new CellWriteHandler() {
                            @Override
                            public void afterCellDispose(CellWriteHandlerContext context) {
                                if (ObjectUtils.isEmpty(finalChoiceMap)) {
                                    setGenerateStyle(context, metaSheets);
                                } else {
                                    setPoistyle(context, metaSheets, finalChoiceMap, dto.getSheetNo(), sheetNos, lastRowNum, dto.getDataStartRowNo());
                                }
                            }
                        }).registerWriteHandler(new CustomCellWriteHandler()).build();
                excelWriter.write(data, writeSheet);
            }
            excelWriter.finish();
        } catch (Exception e) {
            throw new BusinessException(MsgCode.WRITE_STREAM_ERR.code, MsgCode.WRITE_STREAM_ERR.msg);
        }
    }


    private void setPoistyle(CellWriteHandlerContext
                                     context, List<MetaSheetInfo> metaSheets, Map<Integer, String[]> finalChoiceMap
            , Integer sheetNo, List<Integer> sheetNos, Integer lastRowNum, Integer dataStartRow) {
        if (!sheetNos.contains(sheetNo)) {
            Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
            Sheet sheet = workbook.getSheetAt(sheetNo);
            DataValidationHelper helper = sheet.getDataValidationHelper();

            //k为存在下拉数据集的单元格下表 v为下拉数据集
            finalChoiceMap.forEach((k, v) -> {

                //设置下拉单元格的首行 末行 首列 末列 【因为我的业务是第一行是描述，第二行是列头， 第三行是内容
                // 所以入参下标从2开始，暂定1000行，可以写最大行，也可以根据业务而定】
                CellRangeAddressList rangeList = new CellRangeAddressList(dataStartRow, lastRowNum, k, k);

                //下拉列表约束数据
                DataValidationConstraint constraint = helper.createExplicitListConstraint(v);

                //设置约束
                DataValidation validation = helper.createValidation(constraint, rangeList);
                sheet.addValidationData(validation);
            });
            sheetNos.add(sheetNo);
        }
        setGenerateStyle(context, metaSheets);
    }

    private void setGenerateStyle(CellWriteHandlerContext
                                          context, List<MetaSheetInfo> metaSheets) {
        WriteCellData cellData = context.getFirstCellData();
        WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
        writeCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setBorderTop(BorderStyle.THIN);

        //设置单元格纯文本格式
        DataFormatData data = new DataFormatData();
        data.setIndex((short) 49);
        writeCellStyle.setDataFormatData(data);
        writeCellStyle.setLocked(false);

        // 通过单元格所在 sheet， row， column判断是否是异常单元格并调整背景色为红色
        Cell cell = context.getCell();
        Sheet sheet = cell.getSheet();
        String sheetName = sheet.getSheetName();
        for (MetaSheetInfo metaSheetInfo : metaSheets) {
            if (sheetName.equals(metaSheetInfo.getSheetName())) {
                List<FailCell> failCells = metaSheetInfo.getFailCells();

                for (FailCell failCell : failCells) {
                    if (failCell.getCellRowNo() == cell.getRowIndex() &&
                            failCell.getCellColumnNo() == cell.getColumnIndex()) {
                        writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                    }
                }
            }
        }
        WriteFont writeFont = new WriteFont();
        writeFont.setFontName("宋体");
        if (BooleanUtils.isTrue(context.getHead())) {
            writeFont.setBold(true);
        }
        writeCellStyle.setWriteFont(writeFont);
        writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        cellData.setWriteCellStyle(writeCellStyle);
    }

    private void setFailCell(List<MetaSheetInfo> metaSheets, String errIndex) {
        String[] errMsg = errIndex.split(";");
        for (MetaSheetInfo metaSheet : metaSheets) {
            List<FailCell> failCells = new ArrayList<>();
            for (int i = 0; i < errMsg.length; i++) {
                if (metaSheet.getSheetNo() == Integer.valueOf(errMsg[i].split(",")[0])) {
                    FailCell failCell = new FailCell();
                    failCell.setCellRowNo(Integer.valueOf(errMsg[i].split(",")[1]));
                    failCell.setCellColumnNo(Integer.valueOf(errMsg[i].split(",")[2]));
                    failCells.add(failCell);
                }
            }
            metaSheet.setFailCells(failCells);

        }
    }

    /**
     * 获取多个 sheet数据
     *
     * @param inputStream 入参文件流
     * @param param       入参MetaExcelDTO集合
     * @return
     */
    private Map<String, Object> getMoreData(InputStream
                                                    inputStream, List<MetaExcelReadDTO> param) {
        Map<String, Object> resultMap = new HashMap<>();
        List<List<Object>> result = new ArrayList<>();
        List<MetaSheetInfo> metaSheets = new ArrayList<>();
        try {
            ExcelReader excelReader = EasyExcel.read(inputStream).build();
            if (!ObjectUtils.isEmpty(param)) {
                List<ReadSheet> readSheets = new ArrayList<>();
                for (MetaExcelReadDTO dto : param) {
                    List<Object> sheetData = new ArrayList<>();
                    Integer sheetNo = getSheet(dto);
                    Integer dateStartRow = getDateStartRow(dto);
                    Field[] declaredFields = dto.getResultClazz().getDeclaredFields();
                    Integer columnIndex = declaredFields.length;
                    ReadSheet readSheet = EasyExcel.readSheet(sheetNo).head(dto.getResultClazz()).headRowNumber(dateStartRow).registerReadListener(new ReadListener() {
                        @Override
                        public void invoke(Object o, AnalysisContext analysisContext) {
                            sheetData.add(0);
                        }

                        @Override
                        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                            String sheetName = analysisContext.readSheetHolder().getSheetName();
                            MetaSheetInfo metaSheetInfo = new MetaSheetInfo();
                            metaSheetInfo.setSheetNo(sheetNo);
                            metaSheetInfo.setSheetName(sheetName);
                            metaSheets.add(metaSheetInfo);
                            if (null != dto.getDataStartRowNo() && null != dto.getAllowMaxRows() &&
                                    (dto.getDataStartRowNo() + dto.getAllowMaxRows()) < analysisContext.readRowHolder().getRowIndex()) {
                                resultMap.put("rowCheckFlag", "true");
                            }
                            if (columnIndex != analysisContext.readRowHolder().getCellMap().size()) {
                                resultMap.put("modelFlag", "true");
                            }
                            logger.info("{}readfinish!", sheetName);
                        }
                    }).build();
                    readSheets.add(readSheet);
                    result.add(sheetData);
                }
                excelReader.read(readSheets);
                excelReader.finish();
            }
        } catch (Exception e) {
            throw new BusinessException(MsgCode.FILE_ANALYSIS_ERR.code, MsgCode.FILE_ANALYSIS_ERR.msg);
        }

        resultMap.put(SHEET_INFO, metaSheets);
        resultMap.put(DATA, result);
        return resultMap;
    }

    /**
     * 获取数据起始行
     *
     * @param param 入参MetaExcelDTO
     * @return
     */
    private Integer getDateStartRow(MetaExcelReadDTO param) {
        if (null != param.getDataStartRowNo()) {
            return param.getDataStartRowNo();
        } else {
            return 1;
        }
    }

    /**
     * 获取 sheet指定页
     *
     * @param param 入参MetaExcelDTO
     * @ return
     */
    private Integer getSheet(MetaExcelReadDTO param) {
        if (null != param.getSheetNo()) {
            return param.getSheetNo();
        } else {
            return 0;
        }
    }

    /**
     * validate校验 并获取校验异常的 sheet单元格 row+ column
     *
     * @param result          读取数据集
     * @param dataStartRowNum 数据起始行
     * @param num             用于多个 sheet读取计数
     * @param checkMsg        返回错误信息
     * @return
     */
    private Map<String, Object> checkValue(MetaSheetInfo
                                                   metaSheet, List<Object> result, Integer dataStartRowNum
            , Integer num, Map<String, Object> checkMsg, MetaExcelReadDTO meta) {
        StringBuilder errMsg = new StringBuilder();
        StringBuilder errIndex = new StringBuilder();
        if (!ObjectUtils.isEmpty(checkMsg)) {
            errMsg = (StringBuilder) checkMsg.get(ERR_MSG);
            errIndex = (StringBuilder) checkMsg.get(ERR_INDEX);
        }
        ValidatorFactory validatorFactory = Validation.buildDefaultValidatorFactory();

        Validator validator = validatorFactory.getValidator();
        for (int i = 0; i < result.size(); i++) {
            Set<ConstraintViolation<Object>> set = validator.validate(result.get(i));
            if (!CollectionUtils.isEmpty(set)) {
                Iterator<ConstraintViolation<Object>> iterator = set.iterator();
                while (iterator.hasNext()) {
                    ConstraintViolation<Object> next = iterator.next();
                    String fieldName = next.getPropertyPath().toString();
                    try {
                        String errInfo = "sheet:\""+ metaSheet.getSheetName() +
                                "\"第"+ (dataStartRowNum + i + 1) + "行:"+ CellStyleUtil.getCellName(meta.getResultClazz(), fieldName);
                        if (!errMsg.toString().contains(errInfo)) {
                            if (!StringUtils.isEmpty(errMsg.toString())) {
                                errMsg.append(",");
                            }
                            errMsg.append(errInfo);
                            Integer columnNo = CellStyleUtil.getCellColumn(meta.getResultClazz(), fieldName);
                            if (!StringUtils.isEmpty(errIndex.toString())) {
                                errIndex.append(";");
                            }
                            errIndex.append(metaSheet.getSheetNo() + ","+ (dataStartRowNum + i) + ","+ columnNo);
                        }
                    } catch (Exception e) {
                        throw new BusinessException(MsgCode.GET_FIELD_MSG_ERR.code, MsgCode.GET_FIELD_MSG_ERR.msg);
                    }
                }
            }
            checkMsg.put(ERR_MSG, errMsg);
            checkMsg.put(ERR_INDEX, errIndex);
        }
        return checkMsg;
    }

    /**
     * 获取业务方个性化校验结果
     *
     * @param errMsg
     * @param errIndex
     * @param result
     * @param metaSheets
     * @param abstractClazz
     */
    private Map<String, String> getOwnCheckResult(String errMsg, String
            errIndex, Map<String, Object> result
            , List<MetaSheetInfo> metaSheets, Class abstractClazz) {

        //业务方自定义校验逻辑
        Map<String, String> errInfo = new HashMap<>();
        try {
            if (null != abstractClazz) {
                PaOwnCheckExtend paOwnCheckExtend = (PaOwnCheckExtend) abstractClazz.newInstance();
                errInfo = paOwnCheckExtend.ownCheck(errMsg, errIndex, result, metaSheets);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException(MsgCode.OWN_CHECK_FAIL.code, MsgCode.OWN_CHECK_FAIL.msg);
        }
        return errInfo;
    }

    /**
     * 写文件流+标注 validate校验异常单元格背景色为红色
     *
     * @param outputStream 返回流
     * @param metaSheets   每个 sheet相关异常单元格数据
     * @param param        入参对象集
     * @param result       文件解析获取数据集
     */
    private void writeExcel(OutputStream outputStream, List<MetaSheetInfo> metaSheets, List<MetaExcelReadDTO> param, Map<String, Object> result) {
        try {
            DataFormatData data = new DataFormatData();
            data.setIndex((short) 49);
            ExcelWriter excelWriter = EasyExcel.write(outputStream)
                    .registerWriteHandler(new CellWriteHandler() {
                        @Override
                        public void afterCellDispose(CellWriteHandlerContext context) {
                            WriteCellData cellData = context.getFirstCellData();
                            WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
                            writeCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                            writeCellStyle.setBorderBottom(BorderStyle.THIN);
                            writeCellStyle.setBorderLeft(BorderStyle.THIN);
                            writeCellStyle.setBorderRight(BorderStyle.THIN);
                            writeCellStyle.setBorderTop(BorderStyle.THIN);
                            writeCellStyle.setDataFormatData(data);

                            // 通过单元格所在 sheet， row， column判断是否是异常单元格并调整背景色为红色
                            Cell cell = context.getCell();
                            Sheet sheet = cell.getSheet();
                            String sheetName = sheet.getSheetName();

                            for (MetaSheetInfo metaSheetInfo : metaSheets) {
                                if (sheetName.equals(metaSheetInfo.getSheetName())) {
                                    List<FailCell> failCells = metaSheetInfo.getFailCells();
                                    for (FailCell failCell : failCells) {
                                        if (failCell.getCellRowNo() == cell.getRowIndex() && failCell.getCellColumnNo() == cell.getColumnIndex()) {
                                            writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                                        }
                                    }
                                }
                            }
                            WriteFont writeFont = new WriteFont();
                            writeFont.setFontName("宋体");
                            if (BooleanUtils.isTrue(context.getHead())) {
                                writeFont.setBold(true);
                            }
                            writeCellStyle.setWriteFont(writeFont);
                            writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                            cellData.setWriteCellStyle(writeCellStyle);
                        }
                    }).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
            for (int i = 0; i < param.size(); i++) {
                if (!ObjectUtils.isEmpty(result.get(param.get(i).getResultClazz().getSimpleName()))) {
                    WriteSheet writeSheet = EasyExcel.writerSheet(param.get(i).getSheetNo(),
                            metaSheets.get(i).getSheetName()).head(param.get(i).getResultClazz()).build();
                    excelWriter.write((List<Object>) result.get(param.get(i).getResultClazz().getSimpleName()), writeSheet);
                } else {
                    WriteSheet writeSheet = EasyExcel.writerSheet(param.get(i).getSheetNo(), "sheet"+ (i + 1)).head(param.get(i).getResultClazz()).build();
                    excelWriter.write(new ArrayList<>(), writeSheet);
                }
            }
            excelWriter.finish();
        } catch (Exception e) {
            throw new BusinessException(MsgCode.WRITE_STREAM_ERR.code, MsgCode.WRITE_STREAM_ERR.msg);
        }
    }
}
